SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;



-- ----------------------------
-- Table structure for campus_category
-- ----------------------------
DROP TABLE IF EXISTS `campus_category`;
CREATE TABLE `campus_category`
(
    `category_id`   bigint AUTO_INCREMENT                                  NOT NULL COMMENT '分类主键',
    `category_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '分类名',
    `parent_id`     bigint                                             DEFAULT '0' COMMENT '父分类id',
    `order_num`     int                                                DEFAULT '0' COMMENT '显示顺序',
    `slug`          varchar(30)                                            NOT NULL COMMENT '缩略名',
    `description`   varchar(300)                                       DEFAULT NULL COMMENT '描述',
    `type`          char(1)                                            DEFAULT 'N' COMMENT '系统内置（Y是 N否）',
    `icon`          varchar(100)                                       DEFAULT NULL COMMENT '分类图标',
    `status`        char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '状态（0正常 1停用）',
    `del_flag`      bit(1)                                             DEFAULT b'0' COMMENT '逻辑删除(1:已删除，0:未删除)',
    `create_time`   datetime                                           DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `create_user`   bigint                                             DEFAULT NULL COMMENT '创建人',
    `update_time`   datetime                                           DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `update_user`   bigint                                             DEFAULT NULL COMMENT '更新人',
    PRIMARY KEY (`category_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3 COMMENT ='校园平台内容的分类信息';


-- ----------------------------
-- Records of campus_category
-- ----------------------------BEGIN;
INSERT INTO `campus_category` (`category_id`, `category_name`, `parent_id`, `order_num`, `slug`, `description`, `type`,
                               `icon`, `status`, `del_flag`, `create_time`, `create_user`, `update_time`, `update_user`)
VALUES (1, '最新墙', 0, 0, 'newest', '展示最新的校园动态和活动', 'Y', 'new', '0', b'0', '2022-12-30 20:11:41',
        1, '2023-01-01 22:23:49', 1),

       (2, '表白墙', 0, 1, 'confessions', '分享你的表白故事和心情', 'N', 'heart', '0', b'0', '2021-10-15 10:32:00',
        NULL,
        '2023-04-07 19:30:12', 1),

       (3, '表白', 2, 0, 'love_notes', '表达你的爱意和心迹', 'N', 'heart-love', '0', b'0',
        '2021-10-16 11:17:01', NULL, '2023-01-01 22:26:29', 1),

       (4, '分享墙', 0, 2, 'share', '分享你的日常生活和趣事', 'N', 'share', '0', b'0',
        '2021-11-30 20:42:35', NULL, '2022-12-26 17:57:48', 1),

       (5, '日常分享', 4, 0, 'daily_life', '记录和分享生活中的点滴瞬间', 'N', 'share-clock', '0',
        b'0', '2022-12-13 00:16:11', 1, '2022-12-26 17:57:51', 1),

       (6, '交友', 2, 1, 'make_friends', '寻找志同道合的朋友', 'N', 'heart-users', '0', b'0',
        '2023-04-07 19:32:09', 1, '2023-04-07 19:32:08', 1),

       (7, '买卖墙', 0, 3, 'buy_and_sell', '发布或寻找二手物品', 'N', 'shopping', '0', b'0',
        '2023-04-07 19:35:59', 1, '2023-04-07 19:35:59', 1),

       (8, '求购', 7, 0, 'wanted_items', '寻找你需要的二手物品', 'N', 'shopping-buy', '0', b'0',
        '2023-04-07 19:37:08', 1, '2023-04-07 19:37:07', 1),

       (9, '出售', 7, 1, 'selling_items', '出售你不再需要的二手物品', 'N', 'shopping-sell', '0', b'0',
        '2023-04-07 19:38:03', 1, '2023-04-07 19:38:02', 1),

       (10, '综合墙', 0, 4, 'comprehensive', '涵盖各种类型的校园信息', 'N', 'grid', '0', b'0', '2023-04-07 20:01:52', 1,
        '2023-04-07 20:01:51', NULL),

       (11, '实习兼职', 10, 0, 'internship_part_time', '发布实习和兼职机会', 'N',
        'grid-briefcase', '0', b'0', '2023-04-07 20:02:31', 1, '2023-04-07 20:02:30', 1),

       (12, '吐槽', 10, 1, 'roast', '分享你的烦恼和吐槽', 'N', 'grid-fire', '0', b'0',
        '2023-04-07 20:03:07', 1, '2023-04-07 20:03:06', 1);
COMMIT;



-- ----------------------------
-- Table structure for campus_content
-- ----------------------------
-- 校园内容表，用户发布的各类内容信息
DROP TABLE IF EXISTS `campus_content`;
CREATE TABLE `campus_content`
(
    `content_id`   bigint NOT NULL AUTO_INCREMENT COMMENT '内容主键，唯一标识每一条内容',
    `user_id`      bigint                                                  DEFAULT NULL COMMENT '发布内容的用户ID',
    `category_id`  bigint                                                  DEFAULT NULL COMMENT '内容所属类别的ID',
    `content`      varchar(700) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户发表的具体内容文本',
    `status`       tinyint(1)                                              DEFAULT NULL COMMENT '内容的状态：0-待审核, 1-正常显示, 2-已下架, 3-审核未通过被拒绝',
    `type`         tinyint(1)                                              DEFAULT NULL COMMENT '内容的类型：0-纯文字, 1-包含图片, 2-包含视频',
    `file_count`   int                                                     DEFAULT NULL COMMENT '关联的文件数量',
    `love_count`   int                                                     DEFAULT '0' COMMENT '内容收到的点赞数量，默认为0',
    `is_anonymous` tinyint(1)                                              DEFAULT '0' COMMENT '是否匿名发布：0-非匿名，1-匿名',
    `remark`       varchar(500)                                            DEFAULT NULL COMMENT '内容的相关备注信息',
    `del_flag`     bit(1)                                                  DEFAULT b'0' COMMENT '逻辑删除标志：0-未删除，1-已删除',
    `create_time`  datetime                                                DEFAULT CURRENT_TIMESTAMP COMMENT '内容创建的时间戳',
    `create_user`  bigint                                                  DEFAULT NULL COMMENT '创建该内容的用户ID',
    `update_time`  datetime                                                DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '内容最后更新的时间戳',
    `update_user`  bigint                                                  DEFAULT NULL COMMENT '最后更新该内容的用户ID',
    PRIMARY KEY (`content_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3 COMMENT ='校园平台用户发布的各种类型内容的详细信息';

-- ----------------------------
-- Records of campus_content
-- ----------------------------
BEGIN;
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (1, 1, 1, '欢迎使用校园信息墙项目\n开源地址：https://github.com/oddfar/campus\n欢迎大家 Star 和 Fork 支持~', 1, 0,
        0, 0, 1,
        NULL, b'0', '2022-03-05 10:55:04', NULL, '2023-02-26 14:26:33', 1);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (2, 1, 2, 'hello world\nhello world\nhello world\nhello world', 1, 0, 1, 1, 0, NULL, b'0',
        '2022-12-13 00:01:47', NULL, '2023-12-24 23:53:38', 1);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (3, 1, 2,
        '你好，welcome\n开源地址：https://github.com/oddfar/campus\n欢迎大家 Star 和 Fork 支持~', 1, 0, 1, 0, 0, NULL,
        b'0', '2022-12-13 00:01:47', NULL, '2023-12-24 23:53:41', 1);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (4, 2, 1, '你好\n你好\n你好\n你好\n你好\n你好\n你好', 2, 0,
        0, 1, 0, NULL, b'1', '2023-02-26 14:29:46', 2, '2023-09-11 11:35:40', 2);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (5, 2, 1, '1232131', 2, 0, 0, 0, 0, NULL, b'1',
        '2023-03-15 16:38:49', 2, '2023-09-11 11:35:42', 2);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (6, 2, 2, '1111111', 2, 0, 0, 1, 0, NULL, b'1',
        '2023-03-15 16:39:03', 2, '2023-09-11 11:35:44', 2);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (7, 3, 1, '123', 1, 0, 0, 0, 0, NULL, b'1',
        '2023-04-08 14:15:30', 3, '2023-04-08 22:26:13', NULL);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (8, 3, 1, '1', 2, 1, 1, 0, 0, NULL, b'1',
        '2023-04-08 17:50:33', 3, '2023-09-11 11:35:01', 3);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (9, 3, 1, '2', 2, 1, 2, 0, 0, NULL, b'1',
        '2023-04-08 18:18:41', 3, '2023-09-11 11:35:03', 3);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (10, 3, 1, '3', 2, 1, 3, 0, 0, NULL, b'1',
        '2023-04-08 22:25:41', 3, '2023-09-11 11:35:04', 3);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (11, 1, 2, '把回忆拼好给你', 1, 2, 1, 2, 0, NULL, b'1', '2023-04-12 09:01:34',
        1, '2024-01-07 12:05:07', 1);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (12, 1, 2, '柯基', 1, 2, 1, 1, 0, NULL, b'1', '2023-04-12 09:27:41', 1,
        '2024-01-07 12:05:09', 2);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (13, 1, 1, '1', 1, 2, 1, 0, 0, NULL, b'1', '2023-05-10 17:07:44', 1,
        '2024-01-07 12:05:12', 1);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (14, 2, 2, '欢迎大家报考山河大学，我在山河大等你！', 1, 1, 3, 888, 0, NULL,
        b'1', '2023-07-01 11:26:36', 2, '2024-01-07 12:05:14', 1);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (15, 2, 2, '山河大学@柴柴写字', 2, 1, 1, 666, 0, NULL, b'1',
        '2023-07-01 11:17:32', 2, '2024-01-07 12:04:59', 1);
INSERT INTO `campus_content` (`content_id`, `user_id`, `category_id`, `content`, `status`, `type`, `file_count`,
                              `love_count`, `is_anonymous`, `remark`, `del_flag`, `create_time`, `create_user`,
                              `update_time`, `update_user`)
VALUES (16, 2, 2, '山河大学记录片传奇@不张常', 2, 2, 1, 999, 0, NULL, b'1',
        '2023-07-01 11:50:21', 2, '2024-01-07 12:04:57', 1);
COMMIT;



-- ----------------------------
-- Table structure for campus_comment
-- ----------------------------
DROP TABLE IF EXISTS `campus_comment`;
CREATE TABLE `campus_comment`
(
    `comment_id`   bigint NOT NULL AUTO_INCREMENT COMMENT '评论主键',
    `parent_id`    bigint                                                  DEFAULT '0' COMMENT '上级id',
    `one_level_id` bigint                                                  DEFAULT NULL COMMENT '所属的一级评论id',
    `user_id`      bigint                                                  DEFAULT NULL COMMENT '用户id',
    `to_user_id`   bigint                                                  DEFAULT NULL COMMENT '所回复目标评论的用户id',
    `content_id`   bigint                                                  DEFAULT NULL COMMENT '内容id',
    `co_content`   varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '评论内容',
    `ip`           varchar(64)                                             DEFAULT NULL COMMENT '评论时的ip',
    `address`      varchar(100)                                            DEFAULT NULL COMMENT '评论时的地址',
    `del_flag`     bit(1)                                                  DEFAULT b'0' COMMENT '逻辑删除(1:已删除，0:未删除)',
    `create_time`  datetime                                                DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `create_user`  bigint                                                  DEFAULT NULL COMMENT '创建人',
    `update_time`  datetime                                                DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `update_user`  bigint                                                  DEFAULT NULL COMMENT '更新人',
    PRIMARY KEY (`comment_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3 COMMENT ='校园平台内容的评论信息';

-- ----------------------------
-- Records of campus_comment
-- ----------------------------
BEGIN;
INSERT INTO `campus_comment` (`comment_id`, `parent_id`, `one_level_id`, `user_id`, `to_user_id`, `content_id`,
                              `co_content`, `ip`, `address`, `del_flag`, `create_time`, `create_user`, `update_time`,
                              `update_user`)
VALUES (1, 0, 1, 1, NULL, 1, '非常棒的内容！', '192.168.1.1', '北京市', b'0', '2023-01-01 10:00:00', 1,
        '2023-01-01 10:00:00', 1),
       (2, 1, 1, 2, 1, 1, '谢谢夸奖！', '192.168.1.2', '上海市', b'0', '2023-01-01 10:05:00', 2, '2023-01-01 10:05:00',
        2),
       (3, 0, 3, 3, NULL, 2, '我也喜欢这个功能！', '192.168.1.3', '广州市', b'0', '2023-01-02 12:00:00', 3,
        '2023-01-02 12:00:00', 3),
       (4, 3, 3, 4, 3, 2, '赞同！', '192.168.1.4', '深圳市', b'0', '2023-01-02 12:05:00', 4, '2023-01-02 12:05:00', 4);
COMMIT;



-- ----------------------------
-- Table structure for campus_content_love
-- ----------------------------
DROP TABLE IF EXISTS `campus_content_love`;
CREATE TABLE `campus_content_love`
(
    `user_id`     bigint NOT NULL AUTO_INCREMENT COMMENT '用户id',
    `content_id`  bigint NOT NULL COMMENT '文章id',
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`user_id`, `content_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3 COMMENT ='校园平台内容的点赞信息';

-- ----------------------------
-- Records of campus_content_love
-- ----------------------------
BEGIN;
INSERT INTO `campus_content_love` (`user_id`, `content_id`, `create_time`)
VALUES (1, 1, '2023-01-01 10:00:00'),
       (2, 1, '2023-01-01 10:05:00'),
       (3, 2, '2023-01-02 12:00:00'),
       (4, 2, '2023-01-02 12:05:00');
COMMIT;



-- ----------------------------
-- Table structure for campus_content_tag
-- ----------------------------
DROP TABLE IF EXISTS `campus_content_tag`;
CREATE TABLE `campus_content_tag`
(
    `content_id` bigint NOT NULL AUTO_INCREMENT COMMENT '内容id',
    `tag_id`     bigint NOT NULL COMMENT '标签id',
    PRIMARY KEY (`content_id`, `tag_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_general_ci COMMENT ='校园平台内容与标签的关系';

-- ----------------------------
-- Records of campus_content_tag
-- ----------------------------
BEGIN;
INSERT INTO `campus_content_tag` (`content_id`, `tag_id`)
VALUES (1, 1),
       (1, 2),
       (2, 1),
       (2, 3);
COMMIT;



-- Table structure for campus_file
-- ----------------------------
DROP TABLE IF EXISTS `campus_file`;
CREATE TABLE `campus_file`
(
    `file_id`     bigint                                                  NOT NULL AUTO_INCREMENT COMMENT '文件主键',
    `content_id`  bigint   DEFAULT NULL COMMENT '文章id',
    `user_id`     bigint                                                  NOT NULL COMMENT '用户id',
    `url`         varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'url',
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`file_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3 COMMENT ='校园平台内容的文件信息';

-- ----------------------------
-- Records of campus_file
-- ----------------------------
BEGIN;
INSERT INTO `campus_file` (`content_id`, `user_id`, `url`, `create_time`)
VALUES (1, 1, 'http://example.com/file1.jpg', '2023-01-01 10:00:00'),
       (1, 1, 'http://example.com/file2.jpg', '2023-01-01 10:05:00'),
       (2, 2, 'http://example.com/file3.mp4', '2023-01-02 12:00:00');
COMMIT;



-- ----------------------------
-- Table structure for campus_tag
-- ----------------------------
DROP TABLE IF EXISTS `campus_tag`;
CREATE TABLE `campus_tag`
(
    `tag_id`      bigint                                                 NOT NULL AUTO_INCREMENT COMMENT '标签主键',
    `tag_name`    varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标签名',
    `description` varchar(300)                                       DEFAULT NULL COMMENT '描述',
    `status`      char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '状态（0正常 1停用）',
    `del_flag`    bit(1)                                             DEFAULT b'0' COMMENT '逻辑删除(1:已删除，0:未删除)',
    `create_time` datetime                                           DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `create_user` bigint                                             DEFAULT NULL COMMENT '创建人',
    `update_time` datetime                                           DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `update_user` bigint                                             DEFAULT NULL COMMENT '更新人',
    PRIMARY KEY (`tag_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3 COMMENT ='校园平台的内容标签信息';

-- ----------------------------
-- Records of campus_tag
-- ----------------------------
BEGIN;
INSERT INTO `campus_tag` (`tag_id`, `tag_name`, `description`, `status`, `del_flag`, `create_time`, `create_user`,
                          `update_time`, `update_user`)
VALUES (1, '测试标签', '用于测试的标签', '0', b'0', '2022-11-29 20:03:08', 1, '2022-12-01 18:16:16', NULL),
       (2, '梅西', '阿根廷著名足球运动员', '0', b'0', '2022-11-29 20:03:28', 1, '2023-01-16 20:38:34', 1),
       (3, '蔡徐坤', '中国内地男歌手、演员', '0', b'0', '2023-01-16 20:12:20', 1, '2023-01-16 20:13:54', 1);
COMMIT;
